package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.bean.HistoryBean;
import com.bean.RecordBean;
import com.db.JdbcUtil;
import com.exception.DBException;
import com.exception.HistoryException;

public class HistoryDao
{
	private Connection con = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;

	public ArrayList<RecordBean> getHistoryByBarcode(HistoryBean hisBean)
			throws DBException, HistoryException
	{
		String sql = "select a.userID,a.firstname,a.lastname,a.email,d.barcode,d.name,c.borrowDate,c.backDate from lab_user a join lab_record c on a.userID = c.userID join lab_instrument d on d.barcode = c.barcode where d.barcode='" + hisBean.getValue() +"'and c.backDate is not null;";
		return this.getHistory(sql);
	}
	public ArrayList<RecordBean> getHistoryByBarcode_Date(HistoryBean hisBean)
			throws DBException, HistoryException
	{
		String sql = "select a.userID,a.firstname,a.lastname,a.email, d.barcode,d.name,c.borrowDate,c.backDate from lab_user a join lab_record c on a.userID = c.userID join lab_instrument d on d.barcode = c.barcode where d.barcode='" + hisBean.getValue() +"' and c.backDate is not null and c.borrowDate between '" + hisBean.getsDate() + "' and '"+ hisBean.geteDate()+"'";
		return this.getHistory(sql);
	}
	private ArrayList<RecordBean> getHistory(String sql)
			throws DBException, HistoryException
	{
		con = JdbcUtil.getConnection();
		RecordBean recordBean = null;
		ArrayList<RecordBean> list = new ArrayList<RecordBean>();
		try
		{
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next())
			{
				recordBean = new RecordBean();
				recordBean.setUserID(rs.getString(1));
				recordBean.setFirstname(rs.getString(2));
				recordBean.setLastname(rs.getString(3));
				recordBean.setEmail(rs.getString(4));
				recordBean.setBarcode(rs.getString(5));
				recordBean.setInsName(rs.getString(6));
				recordBean.setBorrowDate(rs.getDate(7));
				recordBean.setBackDate(rs.getDate(8));
				list.add(recordBean);
			}
		}
		catch (SQLException e)
		{
			throw new HistoryException("Fail to get the history records");
		}
		finally
		{
			JdbcUtil.free(rs, ps, con);
		}
		return list;
	}

	public ArrayList<RecordBean> getHistoryByUserID(HistoryBean hisBean)
			throws DBException, HistoryException
	{
		String sql = "select a.userID,a.firstname,a.lastname,a.email,d.barcode,d.name,c.borrowDate,c.backDate from lab_user a join lab_record c on a.userID = c.userID join lab_instrument d on d.barcode = c.barcode where a.userID= '" + hisBean.getValue() +"' and c.backDate is not null;";
		return this.getHistory(sql);
	}
	public ArrayList<RecordBean> getHistoryByUserID_date(HistoryBean hisBean)
			throws DBException, HistoryException
	{
		String sql = "select a.userID,a.firstname,a.lastname,a.email,d.barcode,d.name,c.borrowDate,c.backDate from lab_user a join lab_record c on a.userID = c.userID join lab_instrument d on d.barcode = c.barcode where a.userID='" + hisBean.getValue() +"' and c.backDate is not null and c.borrowDate between '" + hisBean.getsDate() + "' and '"+ hisBean.geteDate()+"'";
		return this.getHistory(sql);
	}
}
